

import pandas as pd
import pymysql

class UserInit():

    def __init__(self):
        self.host = '81.68.114.187'
        self.username = 'coolgpt'
        self.password = 'wdjy@880529'
        self.database_name = 'coolgpt'
        self.data = None


    def read_user_csv(self, users_file):
        # 从csv 读取用户信息
        self.data = pd.read_csv(users_file)

    def save_user_to_db(self):
        # 创建一个连接对象，连接到MySQL
        conn = pymysql.connect(host=self.host,  # 主机名
                             user=self.username,  # 用户名
                             password=self.password,  # 密码
                             database=self.database_name,  # 数据库名
                             charset="utf8")  # 编码格式

        # 获取字段名称
        columns = self.data.columns.tolist()

        # 创建游标
        cursor = conn.cursor()

        # 创建表
        table_name = 'gpt_user'
        #create_table_query = f"CREATE TABLE IF NOT EXISTS {table_name} ({', '.join(columns)});"
        #cursor.execute(create_table_query)

        # 插入数据
        for index, row in self.data.iterrows():
            insert_query = f"INSERT INTO {table_name} ({', '.join(columns)}) VALUES ({', '.join(['%s'] * len(columns))})"
            print(insert_query)
            cursor.execute(insert_query, tuple(row))

        # 提交事务并关闭连接
        conn.commit()
        conn.close()


if __name__ == '__main__':
    user_init = UserInit()
    user_init.read_user_csv("data/user_list.csv")
    print(user_init.data.columns.tolist())
    print(user_init.data.values)
    user_init.save_user_to_db()





